insert overwrite table jms_dwd.dwd_tmsnew_shipment_union_base_hi partition(dt)
select
    id
    ,shipment_no
    ,distribution_type
    ,business_attribute
    ,shipment_name
    ,vehicleline_id
    ,vehicleline_code
    ,vehicleline_name
    ,shifts
    ,transport_type
    ,operation_model
    ,vehicle_groupid
    ,vehicle_typegroup
    ,actual_vehicle_groupid
    ,actual_vehicle_typegroup
    ,start_id
    ,start_code
    ,start_name
    ,end_id
    ,end_code
    ,end_name
    ,cnt
    ,mileage
    ,total_runtime
    ,standard_speed
    ,total_intransittime
    ,span_days
    ,actual_mileage
    ,actual_total_runtime
    ,actual_standard_speed
    ,planned_departure_time
    ,planned_arrival_time
    ,actual_departure_time
    ,actual_arrival_time
    ,force_complete_time
    ,shipment_state
    ,vehicle_class_id
    ,vehicle_class_name
    ,plate_number
    ,trailer_number
    ,carrier_id
    ,carrier_type
    ,carrier_name
    ,ori_reg_shift_carrier_id
    ,vehicletype_id
    ,vehicletype_name
    ,load_weight
    ,vehicle_volume
    ,vehicle_door_cnt
    ,driver_name
    ,driver_contact
    ,create_by
    ,create_by_name
    ,create_time
    ,update_by
    ,update_by_name
    ,update_time
    ,overtime_type
    ,overtime_reasons
    ,shipment_type
    ,quotation_model
    ,apply_reason
    ,audit_status
    ,audit_remark
    ,audit_id
    ,auditer
    ,audit_date
    ,apply_reason_item
    ,pic_path
    ,pic_adr
    ,promotion
    ,freight_generate_status
    ,billing_way
    ,settlement_vehicle_type_id
    ,settlement_vehicle_type_name
    ,car_apply_code
    ,bidding_demand_code
    ,is_ballast
    ,special_vehicle_type_id
    ,special_vehicle_type_name
    ,remark
    ,update_times
    ,start_financial_center_id
    ,start_financial_center_code
    ,start_financial_center_desc
    ,end_financial_center_id
    ,end_financial_center_code
    ,end_financial_center_desc
    ,estimate_arrival_time
    ,opt_net_work_code
    ,opt_net_work_name
    ,opt_financial_center_code
    ,opt_financial_center_desc
    ,start_parent_code
    ,start_parent_name
    ,end_parent_code
    ,end_parent_name
    ,complete_time
    ,table_type
    ,dt
from (
    select
        id
        ,shipment_no
        ,vehicleline_type as distribution_type
        ,business_attribute
        ,shipment_name
        ,vehicleline_id
        ,vehicleline_code
        ,vehicleline_name
        ,shifts
        ,transport_type
        ,operation_model
        ,vehicle_groupid
        ,vehicle_typegroup
        ,vehicletype_id actual_vehicle_groupid
        ,vehicletype_name actual_vehicle_typegroup
        ,start_id
        ,start_code
        ,start_name
        ,end_id
        ,end_code
        ,end_name
        ,cnt
        ,mileage
        ,total_runtime
        ,standard_speed
        ,total_intransittime
        ,span_days
        ,null actual_mileage
        ,null actual_total_runtime
        ,null actual_standard_speed
        ,planned_departure_time
        ,planned_arrival_time
        ,actual_departure_time
        ,actual_arrival_time
        ,null force_complete_time
        ,shipment_state
        ,vehicle_class_id
        ,vehicle_class_name
        ,plate_number
        ,trailer_number
        ,carrier_id
        ,carrier_type
        ,carrier_name
        ,ori_reg_shift_carrier_id
        ,vehicletype_id
        ,vehicletype_name
        ,load_weight
        ,null vehicle_volume
        ,vehicle_door_cnt
        ,driver_name
        ,driver_contact
        ,create_by
        ,create_by_name
        ,create_time
        ,update_by
        ,update_by_name
        ,update_time
        ,overtime_type
        ,overtime_reasons
        ,shipment_type
        ,quotation_model
        ,apply_reason
        ,audit_status
        ,audit_remark
        ,audit_id
        ,auditer
        ,audit_date
        ,apply_reason_item
        ,pic_path
        ,pic_adr
        ,promotion
        ,freight_generate_status
        ,billing_way
        ,settlement_vehicle_type_id
        ,settlement_vehicle_type_name
        ,car_apply_code
        ,bidding_demand_code
        ,is_ballast
        ,special_vehicle_type_id
        ,special_vehicle_type_name
        ,null remark
        ,null update_times
        ,null start_financial_center_id
        ,null start_financial_center_code
        ,null start_financial_center_desc
        ,null end_financial_center_id
        ,null end_financial_center_code
        ,null end_financial_center_desc
        ,null estimate_arrival_time
        ,null opt_net_work_code
        ,null opt_net_work_name
        ,null opt_financial_center_code
        ,null opt_financial_center_desc
        ,null start_parent_code
        ,null start_parent_name
        ,null end_parent_code
        ,null end_parent_name
        ,null complete_time
        ,1 as  table_type
        ,1 as row_id
        ,substr(planned_departure_time,1,10) as dt
    from jms_ods.yl_tmsnew_tms_shipment_hf
    where dt = '{{ execution_date | cst_hour }}'
    and substr(planned_departure_time,1,10) >= '{{ execution_date | date_add(-90) | cst_ds }}'
    and substr(planned_departure_time,1,10) <= '{{ execution_date | date_add(7) | cst_ds }}'

    union all

    select
        id
        ,shipment_no
        ,distribution_type
        ,business_attribute
        ,shipment_name
        ,line_id as vehicleline_id
        ,line_code as vehicleline_code
        ,line_name as vehicleline_name
        ,shifts
        ,null transport_type
        ,null operation_model
        ,vehicle_groupid
        ,vehicle_typegroup
        ,actual_vehicle_groupid
        ,actual_vehicle_typegroup
        ,start_id
        ,start_code
        ,start_name
        ,end_id
        ,end_code
        ,end_name
        ,cnt
        ,mileage
        ,total_runtime
        ,standard_speed
        ,null total_intransittime
        ,null span_days
        ,actual_mileage
        ,actual_total_runtime
        ,actual_standard_speed
        ,planned_departure_time
        ,planned_arrival_time
        ,actual_departure_time
        ,actual_arrival_time
        ,force_complete_time
        ,shipment_state
        ,null vehicle_class_id
        ,null vehicle_class_name
        ,plate_number
        ,null trailer_number
        ,carrier_id
        ,carrier_type
        ,carrier_name
        ,ori_reg_shift_carrier_id
        ,actual_vehicle_groupid vehicletype_id
        ,actual_vehicle_typegroup vehicletype_name
        ,load_weight
        ,vehicle_volume
        ,vehicle_door_cnt
        ,driver_name
        ,driver_contact
        ,create_by
        ,create_by_name
        ,create_time
        ,update_by
        ,update_by_name
        ,update_time
        ,overtime_type
        ,null overtime_reasons
        ,null shipment_type
        ,null quotation_model
        ,null apply_reason
        ,null audit_status
        ,null audit_remark
        ,null audit_id
        ,null auditer
        ,null audit_date
        ,null apply_reason_item
        ,pic_path
        ,pic_adr
        ,null promotion
        ,null freight_generate_status
        ,null billing_way
        ,null settlement_vehicle_type_id
        ,null settlement_vehicle_type_name
        ,null car_apply_code
        ,null bidding_demand_code
        ,null is_ballast
        ,null special_vehicle_type_id
        ,null special_vehicle_type_name
        ,remark
        ,update_times
        ,start_financial_center_id
        ,start_financial_center_code
        ,start_financial_center_desc
        ,end_financial_center_id
        ,end_financial_center_code
        ,end_financial_center_desc
        ,estimate_arrival_time
        ,opt_net_work_code
        ,opt_net_work_name
        ,opt_financial_center_code
        ,opt_financial_center_desc
        ,start_parent_code
        ,start_parent_name
        ,end_parent_code
        ,end_parent_name
        ,complete_time
        ,2 as table_type
        ,row_number() over(partition by shipment_no order by update_time desc) as row_id
        ,substr(planned_departure_time,1,10) as dt
    from jms_ods.yl_tmsnew_branch_shipment_hi
    where dt >= concat('{{ execution_date | date_add(-95) | cst_ds }}','-','00')
    and substr(planned_departure_time,1,10) >= '{{ execution_date | date_add(-90) | cst_ds }}'
    and substr(planned_departure_time,1,10) <= '{{ execution_date | date_add(7) | cst_ds }}'
) tmp
where row_id = 1
distribute by dt
;